/**
 * 
 */
package cts.com.dbmanager;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Map;

import cts.com.connection.DbConnection;
import cts.com.exception.TransactionException;
import cts.com.jaxb.TransactionBean;

/**
 * @author Sourish
 * 
 */
public class DepositWithdrawlCash {

	String tableNameACInfo = "AccountInformation";
	String tableNameTransaction = "TransactionHistory";

	public double getAvailableBalance(String accountNumber)
			throws SQLException, ClassNotFoundException {
		double balance = 0;
		String availBalanceQuery = " SELECT balance from " + tableNameACInfo
				+ " where Account_Id='" + accountNumber + "'";
		Connection connection = null;
		PreparedStatement pStatement = null;
		connection = new DbConnection().getConnection();
		pStatement = connection.prepareStatement(availBalanceQuery);
		ResultSet resultSet = pStatement.executeQuery();
		while (resultSet.next()) {
			balance = resultSet.getDouble("balance");
		}
		pStatement.close();
		connection.close();
		return balance;
	}

	public Map<Integer, String[]> getDataFromTablesUsingCondition(
			String columnNames, String tableNames, String condition)
			throws SQLException, ClassNotFoundException {
		String[] resultingColumns = columnNames.split(",");
		String selectQuery = "SELECT " + columnNames + " from " + tableNames
				+ " " + condition;
		System.out.println(selectQuery);
		Connection connection = null;
		PreparedStatement pStatement = null;
		connection = new DbConnection().getConnection();
		pStatement = connection.prepareStatement(selectQuery);
		ResultSet resultSet = pStatement.executeQuery();
		Map<Integer, String[]> resultMap = new HashMap<Integer, String[]>();
		int count = 1;
		while (resultSet.next()) {
			String[] arrStr = new String[resultingColumns.length];
			for (int i = 0; i < resultingColumns.length; i++) {
				arrStr[i] = (String) resultSet.getString(i + 1);
			}
			resultMap.put(count, arrStr);
			count++;
		}
		pStatement.close();
		connection.close();
		return resultMap;
	}

	public boolean isAmtDeposited(TransactionBean depositTransBean)
			throws SQLException, ClassNotFoundException, TransactionException {
		if (depositTransBean.getAmount() <= 0.00d) {
			throw new TransactionException(
					"Amount to deposited should be greater than 0");
		}
		return depositTransBean.getAmount() > 0 ? isBatchExecutedSuccessfully(depositTransBean)
				: false;
	}

	public boolean isBatchExecutedSuccessfully(TransactionBean transBean)
			throws SQLException, ClassNotFoundException {

		double currentBalance = getAvailableBalance(transBean
				.getFromAccountNo()) + transBean.getAmount();
		String updateBalance = "Update " + tableNameACInfo + " set balance="
				+ currentBalance + " where account_Id='"
				+ transBean.getFromAccountNo() + "'";
		transBean.setCurrentBalance(currentBalance);
		String insertBalance = "Insert into "
				+ tableNameTransaction
				+ "(PrimaryAccountNo, PrimaryIFSC, ExternalAccountNo, ExternalIFSC, "
				+ "TransactionType, Amount, Description, TransactionDateTime, CurrentBalance)"
				+ " values('" + transBean.getFromAccountNo() + "','"
				+ transBean.getFromIFSC() + "','" + transBean.getToAccountNo()
				+ "','" + transBean.getToIFSC() + "','"
				+ transBean.getTransType() + "',"
				+ Math.abs(transBean.getAmount()) + ",'"
				+ transBean.getDescription() + "','"
				+ transBean.getTransTimeStamp() + "',"
				+ transBean.getCurrentBalance() + ")";

		System.out.println(updateBalance);
		System.out.println(insertBalance);
		String[] queries = { updateBalance, insertBalance };

		Statement statement = null;
		boolean isTransactionOccurredSuccessfully = false;
		Connection connection = null;
		int[] success = null;
		try {
			connection = new DbConnection().getConnection();
			connection.setAutoCommit(false);
			statement = connection.createStatement();
			for (String query : queries) {
				statement.addBatch(query);
			}
			success = statement.executeBatch();
			for (int i : success) {
				System.out.println(i);
				if (i == 1) {
					isTransactionOccurredSuccessfully = true;
				} else {
					isTransactionOccurredSuccessfully = false;
					break;
				}
			}
		} catch (Exception e) {
			isTransactionOccurredSuccessfully = false;
			try {
				connection.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			System.err.println("RolledBack:\n" + e);
		} finally {

			try {
				statement.close();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				System.err.println("Statement Close Exception:" + e1);
			}
			try {
				connection.setAutoCommit(true);
				connection.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				System.err.println("Connection Close Exception:" + e);

			}

		}
		return isTransactionOccurredSuccessfully;
	}

	public boolean isAmtWithdrawled(TransactionBean withdrawTransBean)
			throws SQLException, ClassNotFoundException, TransactionException {
		double availAbleBal = getAvailableBalance(withdrawTransBean
				.getFromAccountNo());
		if (availAbleBal > 0) {
			if (withdrawTransBean.getAmount() > 0
					&& withdrawTransBean.getAmount() <= availAbleBal) {
				withdrawTransBean.setAmount(withdrawTransBean.getAmount() * -1);
				return isBatchExecutedSuccessfully(withdrawTransBean);
			} else {
				throw new TransactionException(
						"Either amount you want to withdraw is less than 0 OR its greater than Available Balance");
			}
		} else {
			throw new TransactionException("Available balance is 0");
		}
	}

	public static void main(String[] args) throws SQLException,
			ClassNotFoundException, TransactionException {
		DepositWithdrawlCash dCash = new DepositWithdrawlCash();
		String columnNames = " b.Beneficiary_Ac_No,b.Beneficiary_Name,b.Beneficiary_IFSC,b.Beneficiary_Bank ";
		String tableNames = "AccountInformation a,BENEFICIARY_ACCOUNT b";
		String condition = " where a.Account_Id=b.Primary_Account_Id and a.Bank_Name=b.Beneficiary_Bank ";
		Map<Integer, String[]> map = dCash.getDataFromTablesUsingCondition(
				columnNames, tableNames, condition);
		for (String[] str : map.values()) {
			for (String s : str) {
				System.out.print(s + "\t");
			}
			System.out.println();
		}
	}
}
